import shutil
from configparser import ConfigParser

import openpyxl
from openpyxl.styles import Alignment, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl.comments import Comment

from tools_pmc import (today_str1, lj_1, lj_CBOM, lj_config, vlookup, lj_CBOM_y, lj_info,
                       table_array_wlxxb,
                       detect_date_str, detect_date_int, vlookup_pb, str_pb, lj_data
                       )

#  导出采购货期
namecghq = "销售预测毛需求09-11月 - to采购终版.xlsx"
ljcg = "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/"
ljcghq = ljcg + namecghq
wbcghq = openpyxl.load_workbook(ljcghq)
ws_cghq = wbcghq.worksheets[0]
list1 = [1, 1, 1, 1, 1]
for i in range(1, ws_cghq.max_column + 1):
    if ws_cghq.cell(1, i).value is not None:
        if "首批到货数量" in ws_cghq.cell(1, i).value:
            list1[0] = i
        if "批次交期" in ws_cghq.cell(1, i).value:
            list1[1] = i
        if "下批到货" in ws_cghq.cell(1, i).value:
            list1[2] = i
        # if "交期变动" in ws_cghq.cell(1, i).value:
        #     list1[3] = i
        if "情况" in ws_cghq.cell(1, i).value:
            list1[4] = i
print("货期信息位置：", list1)

wb_info = openpyxl.load_workbook(lj_info)
wb_info.create_sheet("采购货期反馈" + today_str1, 0)
ws = wb_info.worksheets[0]
a = 2
for i in range(2, ws_cghq.max_row + 1):
    ws.cell(a, 1).value = str(ws_cghq.cell(i, 1).value)
    ws.cell(a, 2).value = str(ws_cghq.cell(i, 2).value)
    ws.cell(a, 3).value = str(ws_cghq.cell(i, 3).value)
    ws.cell(a, 4).value = ws_cghq.cell(i, list1[0]).value  # 首批到货数量
    if ws_cghq.cell(i, list1[1]).value is not None:
        try:
            ws.cell(a, 5).value = ws_cghq.cell(i, list1[1]).value.strftime("%Y/%m/%d")
        except:
            if type(ws_cghq.cell(i, list1[1]).value) == int:
                ws.cell(a, 5).value = detect_date_int(ws_cghq.cell(i, list1[1]).value)
            else:
                ws.cell(a, 5).value = detect_date_str(ws_cghq.cell(i, list1[1]).value)
    ws.cell(a, 6).value = ws_cghq.cell(i, list1[2]).value  # 下批到货数量/时间
    if ws_cghq.cell(i, list1[3]).value is not None:
        temp1 = ws_cghq.cell(i, list1[3]).value
    else:
        temp1 = ""
    if ws_cghq.cell(i, list1[4]).value is not None:
        temp2 = ws_cghq.cell(i, list1[4]).value
    else:
        temp2 = ""
    ws.cell(a, 7).value = temp2
    a += 1

list1 = ['品号', '品名', '规格', '首批到货数量', '批次交期', '下批到货数量/时间', '情况说明']
list2 = [14, 35, 35, 8, 14, 30, 20]
for i in range(1, 8):
    ws.cell(1, i).value = list1[i - 1]
    ws.column_dimensions[get_column_letter(i)].width = list2[i - 1]

wb_info.save(lj_info)

# 导入并处理BOM信息表
wb_data = openpyxl.load_workbook(lj_data, data_only=True)
ws_data_pb = wb_data.worksheets[1]  # 拼版数据

wbCBOM = openpyxl.load_workbook(lj_CBOM_y)
ws_CBOM = wbCBOM.worksheets[0]
ws_CBOM.unmerge_cells('A1:L1')
ws_CBOM.freeze_panes = 'M3'

table_array_data_pb = list(ws_data_pb['A1':'I' + str(ws_data_pb.max_row)])
for i in range(1, ws_CBOM.max_column + 1):
    if ws_CBOM.cell(2, i).value is not None:
        if str(ws_CBOM.cell(2, i).value)[0] == "3":
            temp = vlookup_pb(ws_CBOM.cell(2, i).value[0:12], table_array_data_pb)
            if temp is not None:
                comment = Comment(str_pb(temp), 'shen')
                comment.width = 150
                comment.height = 50
                ws_CBOM.cell(2, i).comment = comment


for i in range(3, ws_CBOM.max_row + 1):
    ws_CBOM.cell(i, 1).data_type = "int"
    ws_CBOM.cell(i, 5).data_type = "int"
    ws_CBOM.cell(i, 6).data_type = "int"
    ws_CBOM.cell(i, 1).number_format = "0"
    ws_CBOM.cell(i, 5).number_format = "0"
    ws_CBOM.cell(i, 6).number_format = "0"

ws_CBOM.column_dimensions['B'].width = 0.0001
list1 = [0, 1, 2, 3, 4, 8, 9, 10, 11, 14, 15, 16, 17, 18]
for i in list1:
    ws_CBOM.column_dimensions[get_column_letter(ws_CBOM.max_column - i)].width = 0.0001

ws_CBOM["A1"] = ""
ws_CBOM["C1"] = "成品BOM信息表" + today_str1
ws_CBOM["J2"] = "良品（包材、螺丝"
ws_CBOM["K2"] = "欠料数量"
ws_CBOM["L2"] = "可用数量"

fill = PatternFill(patternType="solid", fgColor="FFFF00", bgColor="FFFF00")
for i in range(1, 13):
    ws_CBOM.cell(1, i).fill = fill
ws_CBOM["J2"].fill = fill
ws_CBOM["K2"].fill = fill
ws_CBOM["L2"].fill = fill
alignment = Alignment(horizontal='center', vertical='center', wrap_text=True, shrink_to_fit=False)
ws_CBOM["J2"].alignment = alignment
ws_CBOM["K2"].alignment = alignment
ws_CBOM["L2"].alignment = alignment

max_column = ws_CBOM.max_column
ws_CBOM.cell(2, max_column + 1).alignment = alignment
ws_CBOM.cell(2, max_column + 2).alignment = alignment
ws_CBOM.cell(2, max_column + 3).alignment = alignment

for i in range(1, ws_CBOM.max_column + 1):
    if ws_CBOM.cell(1, i).value == "缺料统计":
        a_qltj = i

table_array_cghq = list(ws['A1':'G' + str(ws_cghq.max_row)])
for i in range(3, (ws_CBOM.max_row + 1)):
    ws_CBOM.cell(i, 5).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_wlxxb, 5)
    ws_CBOM.cell(i, 6).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_wlxxb, 6)
    ws_CBOM.cell(i, 7).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_wlxxb, 7)
    ws_CBOM.cell(i, 11).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_wlxxb, 13)
    ws_CBOM.cell(i, 12).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_wlxxb, 14)
    # noinspection PyUnboundLocalVariable
    ws_CBOM.cell(i, a_qltj).value = "=ROUND(L" + str(i) + "-" + get_column_letter(a_qltj - 1) + str(i) + ",3)"  # 修正缺料合计
    ws_CBOM.cell(i, max_column + 1).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_cghq, 4)
    ws_CBOM.cell(i, max_column + 2).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_cghq, 5)
    ws_CBOM.cell(i, max_column + 3).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_cghq, 6)
    ws_CBOM.cell(i, max_column + 4).value = vlookup(ws_CBOM.cell(i, 1).value, table_array_cghq, 7)

list1 = ['首批到货数量', '批次交期', '下批到货数量/时间', '情况说明']
list2 = [8, 14, 30, 20]
list3 = [max_column + 1, max_column + 2, max_column + 3, max_column + 4]
for i in range(0, 4):
    ws_CBOM.cell(2, list3[i]).value = list1[i]
    ws_CBOM.column_dimensions[get_column_letter(list3[i])].width = list2[i]

for i in range(3, (ws_CBOM.max_row + 1)):
    if ws_CBOM.cell(i, 1).value == "102030010010":
        ws_CBOM.cell(i, 5).value = "由102030010001，102040010018,104150010036组成"
    if ws_CBOM.cell(i, 1).value == "102030010014":
        ws_CBOM.cell(i, 5).value = "由110010010854,102030010001,104150010036组成"
    if ws_CBOM.cell(i, 1).value == "102030010015":
        ws_CBOM.cell(i, 5).value = "由102040010028,102030010001,104150010036组成"

ws_CBOM.views.sheetView[0].zoomScale = 70
ws_CBOM.auto_filter.ref = "A2:BAA" + str(ws_CBOM.max_row)

wbCBOM.save(lj_CBOM)

cfg = ConfigParser()
cfg.read(lj_config)
if not cfg.get('set', 'Test mode'):
    shutil.copy(lj_CBOM, lj_1)
print(today_str1 + ":CBOM succeed!")
